// mysqlUtil.js
const mysql = require('mysql2/promise');
const config = require('../config'); // 引入配置文件

// 创建数据库连接池
const pool = mysql.createPool({
    ...config.mysql,
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

// 封装查询方法
async function query(sql, params = []) {
    const connection = await pool.getConnection();
    try {
        const [results, fields] = await connection.execute(sql, params);
        return results;
    } catch (error) {
        throw error;
    } finally {
        connection.release();
    }
}

// 封装新增方法
async function insert(sql, params = []) {
    const connection = await pool.getConnection();
    try {
        const [results] = await connection.execute(sql, params);
        return results.insertId;
    } catch (error) {
        throw error;
    } finally {
        connection.release();
    }
}

// 封装修改方法
async function update(sql, params = []) {
    const connection = await pool.getConnection();
    try {
        const [results] = await connection.execute(sql, params);
        return results.affectedRows;
    } catch (error) {
        throw error;
    } finally {
        connection.release();
    }
}

// 封装删除方法
async function deleteRecord(sql, params = []) {
    const connection = await pool.getConnection();
    try {
        const [results] = await connection.execute(sql, params);
        return results.affectedRows;
    } catch (error) {
        throw error;
    } finally {
        connection.release();
    }
}

// 封装获取连接方法
async function getConnection() {
    return await pool.getConnection();
}

// 封装关闭连接方法
async function closeConnection(connection) {
    if (connection) {
        connection.release();
    }
}

module.exports = {
    query,
    insert,
    update,
    deleteRecord,
    getConnection,
    closeConnection
};